﻿CREATE FUNCTION dbo.GetTargetInformation
	(
		@TargetID AS int,
		@ScopeID AS int
	)  
RETURNS 
	@Infomation TABLE 
	(
		Server varchar(50),
		ServerId int,
		Service varchar(50),
		ServiceId int,
		[Database] varchar(200),
		DatabaseId int
	)
AS
BEGIN
	

	IF (@ScopeID=1)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				'' AS Service,
				0,
				'' AS [Database],
				0
			FROM	
				dbo.Server AS S
			WHERE
				ServerID = @TargetID
	END
	ELSE IF (@ScopeID=2)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				SE.Name AS Service,
				SE.ServiceID,
				'' AS [Database],
				0
			FROM	
				dbo.Server AS S INNER JOIN
				dbo.Service AS SE ON S.ServerID=SE.ServerID
				
			WHERE
				ServiceID = @TargetID
	
	END
	ELSE IF (@ScopeID=3)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				SE.Name AS Service,
				SE.ServiceID,
				D.DatabaseName AS [Database],
				D.DatabaseID
			FROM	
				dbo.Server AS S INNER JOIN
				dbo.Service AS SE ON S.ServerID=SE.ServerID INNER JOIN
				dbo.[Database] AS D ON SE.ServiceID =D.ServiceID
				
			WHERE
				D.DatabaseID= @TargetID
	
	END
	ELSE IF (@ScopeID=4)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				SE.Name AS Service,
				SE.ServiceID,
				D.DatabaseName AS [Database],
				D.DatabaseID
			FROM	
				dbo.Server AS S INNER JOIN
				dbo.Service AS SE ON S.ServerID=SE.ServerID INNER JOIN
				dbo.[Database] AS D ON SE.ServiceID =D.ServiceID INNER JOIN
				dbo.DatabaseFile AS DF ON D.DatabaseID=DF.DatabaseID
				
			WHERE
				DF.DatabaseFileID= @TargetID
	
	END
	ELSE IF (@ScopeID=5)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				SE.Name AS Service,
				SE.ServiceID,
				D.DatabaseName AS [Database],
				D.DatabaseID
			FROM	
				dbo.Server AS S INNER JOIN
				dbo.Service AS SE ON S.ServerID=SE.ServerID INNER JOIN
				dbo.[Database] AS D ON SE.ServiceID =D.ServiceID INNER JOIN
				dbo.[Table] AS T ON D.DatabaseID=T.DatabaseID
				
			WHERE
				T.TableID= @TargetID
	
	END
	ELSE IF (@ScopeID=6)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				S.Name AS Server,
				S.ServerID,
				SE.Name AS Service,
				SE.ServiceID,
				D.DatabaseName AS [Database],
				D.DatabaseID
			FROM	
				dbo.Server AS S INNER JOIN
				dbo.Service AS SE ON S.ServerID=SE.ServerID INNER JOIN
				dbo.[Database] AS D ON SE.ServiceID =D.ServiceID INNER JOIN
				DBO.[Table] AS T ON D.DatabaseID=T.DatabaseID INNER JOIN
				dbo.[Index] AS I ON T.TableID=I.TableID
				
			WHERE
				I.IndexID= @TargetID
	
	END


	RETURN
END
